﻿using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace MyFurion.Unility.Generic
{
    /// <summary>
    /// Excel导入导出
    /// </summary>
    public class ExcelHelper : ITransient
    {
        #region NPOIExcel导入
        /// <summary>
        /// 将Excel表转换为Datatable数据
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="fileType">导入的excel数据格式</param>
        /// <param name="errMsg"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable ExcelToDatatable(Stream stream, string fileType, out string errMsg, string sheetName = null)
        {
            errMsg = "";
            DataTable dt = new DataTable();
            ISheet sheet = null;
            IWorkbook workbook = null;
            try
            {
                if (fileType != ".xlsx" && fileType != ".xls")
                {
                    errMsg = "请选择Excel格式文件！";
                    return dt;
                }
                //2007以上版本
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                //2007以下版本
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook(stream);
                }
                if (!string.IsNullOrWhiteSpace(sheetName))
                {
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    //遍历字段名称
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = "";// cell.StringCellValue.Trim();
                            //判断是否为数字型
                            if (cell.CellType == CellType.Numeric)
                            {
                                //判断是否为日期类型
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    cellValue = DateTime.Parse(cell.DateCellValue.ToString()).ToString("yyyy-MM-dd");
                                }
                                else
                                {
                                    cellValue = cell.ToString().Trim();
                                }
                            }
                            else
                            {
                                cellValue = cell.StringCellValue.Trim();
                            }
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                DataColumn dataColumn = new DataColumn(cellValue);
                                dt.Columns.Add(dataColumn);
                            }
                        }
                    }
                    DataRow dataRow = null;
                    //遍历行
                    for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
                    {
                        IRow row = sheet.GetRow(j);
                        dataRow = dt.NewRow();
                        if (row == null || row.FirstCellNum < 0)
                        {
                            continue;
                        }
                        //遍历列
                        for (int i = row.FirstCellNum; i < cellCount; i++)
                        {
                            ICell cellData = row.GetCell(i);
                            if (cellData != null)
                            {
                                //判断是否为数字型
                                if (cellData.CellType == CellType.Numeric)
                                {
                                    //判断是否为日期类型
                                    if (DateUtil.IsCellDateFormatted(cellData))
                                    {
                                        dataRow[i] = DateTime.Parse(cellData.DateCellValue.ToString()).ToString("yyyy-MM-dd");
                                    }
                                    else
                                    {
                                        dataRow[i] = cellData.ToString().Trim();
                                    }
                                }
                                else if (cellData.CellType == CellType.Formula)
                                {
                                    //公式类型的
                                    row.GetCell(i).SetCellType(CellType.String);
                                    dataRow[i] = cellData.StringCellValue.Trim();
                                }
                                else
                                {
                                    dataRow[i] = cellData.ToString().Trim();
                                }
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
                else
                {
                    errMsg = "没有获取到Excel中的数据表！";
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            if (workbook != null)
            {
                workbook.Close();
            }
            return dt;
        }
        /// <summary>
        /// 将Excel多个sheet表转换为DataSet数据
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="sheetNames"></param>
        /// <param name="fileType"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public DataSet ExcelToDataSet(Stream stream, string[] sheetNames, string fileType, out string errMsg)
        {
            DataSet ExcelData = new DataSet();
            errMsg = "";
            IWorkbook workbook = null;
            try
            {
                if (fileType != ".xlsx" && fileType != ".xls")
                {
                    errMsg = "传入的不是Excel文件！";
                    return ExcelData;
                }
                //2007以上版本
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                //2007以下版本
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook(stream);
                }
                if (sheetNames != null)
                {
                    foreach (var sheetName in sheetNames)
                    {
                        ISheet sheet = workbook.GetSheet(sheetName);
                        var table = GetTable(sheet, out errMsg);
                        table.TableName = sheetName;
                        ExcelData.Tables.Add(table);
                    }
                }
                else
                {
                    ISheet sheet = workbook.GetSheetAt(0);
                    var table = GetTable(sheet, out errMsg);
                    ExcelData.Tables.Add(table);
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            workbook.Close();
            return ExcelData;
        }
        /// <summary>
        /// 读取excel表数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public DataTable GetTable(ISheet sheet, out string errMsg)
        {
            errMsg = "";
            try
            {
                DataTable table = new DataTable();
                //获取sheet的首行
                IRow headerRow = sheet.GetRow(0);
                //一行最后一个方格的编号 即总的列数
                int cellCount = headerRow.LastCellNum;
                //用户生成表头
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                DataRow dataRow = null;
                //遍历行
                for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
                {
                    IRow row = sheet.GetRow(j);
                    dataRow = table.NewRow();
                    if (row == null || row.FirstCellNum < 0)
                    {
                        continue;
                    }
                    //遍历列
                    for (int i = row.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cellData = row.GetCell(i);
                        if (cellData != null)
                        {
                            //判断是否为数字型
                            if (cellData.CellType == CellType.Numeric)
                            {
                                //判断是否为日期类型
                                if (DateUtil.IsCellDateFormatted(cellData))
                                {
                                    dataRow[i] = DateTime.Parse(cellData.DateCellValue.ToString()).ToString("yyyy-MM-dd");
                                }
                                else
                                {
                                    dataRow[i] = cellData.ToString().Trim();
                                }
                            }
                            else if (cellData.CellType == CellType.Formula)
                            {
                                //公式类型的
                                row.GetCell(i).SetCellType(CellType.String);
                                dataRow[i] = cellData.StringCellValue.Trim();
                            }
                            else
                            {
                                dataRow[i] = cellData.ToString().Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                return table;
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                throw;
            }
        }
        /// <summary>
        /// 数据验证
        /// </summary>
        /// <param name="inputValue">单元格数据</param>
        /// <param name="columnName">列名</param>
        /// <param name="rowIndex">行标</param>
        /// <param name="maxlength">输入最大限制长度</param>
        /// <param name="dataType">数据格式</param>
        /// <param name="isRequired">是否必填项验证</param>
        /// <param name="validLength">是否验证长度限制</param>
        /// <param name="errMsg">错误提示</param>
        /// <param name="remark">数据格式验证特别说明</param>
        private bool DataValid(string inputValue, string columnName, int rowIndex, int maxlength, string dataType, bool isRequired, bool validLength, out string errMsg, string remark = "")
        {
            //必填项验证
            if (isRequired)
            {
                if (string.IsNullOrEmpty(inputValue))
                {
                    errMsg = String.Format("第{0}行{1}不能为空，请修正后重试！", rowIndex, columnName);
                    return false;
                }
            }
            //数据格式验证
            if (!string.IsNullOrEmpty(dataType))
            {
                if (dataType == "DateTime")
                {
                    DateTime dateTime;
                    bool validResult = true;
                    if (isRequired)
                    {
                        validResult = DateTime.TryParse(inputValue, out dateTime);
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(inputValue))
                        {
                            validResult = DateTime.TryParse(inputValue, out dateTime);
                        }
                    }
                    if (!validResult)
                    {
                        errMsg = String.Format("第{0}行{1}数据格式错误，正确格式为日期（yyyy-MM-dd），请修正后重试！", rowIndex, columnName);
                        if (!string.IsNullOrEmpty(remark))
                        {
                            errMsg += "(注：" + remark + ")";
                        }
                        return false;
                    }
                }
                else if (dataType == "decimal")
                {
                    decimal validD;
                    bool validResult = true;
                    if (isRequired)
                    {
                        validResult = decimal.TryParse(inputValue, out validD);
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(inputValue))
                        {
                            validResult = decimal.TryParse(inputValue, out validD);
                        }
                    }
                    if (!validResult)
                    {
                        errMsg = String.Format("第{0}行{1}数据格式错误，正确格式为数字，请修正后重试！", rowIndex, columnName);
                        if (!string.IsNullOrEmpty(remark))
                        {
                            errMsg += "(注：" + remark + ")";
                        }
                        return false;
                    }
                }
                else if (dataType == "int")
                {
                    int validint;
                    bool validResult = true;
                    if (isRequired)
                    {
                        validResult = int.TryParse(inputValue, out validint);
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(inputValue))
                        {
                            validResult = int.TryParse(inputValue, out validint);
                        }
                    }
                    if (!validResult)
                    {
                        errMsg = String.Format("第{0}行{1}数据格式错误，正确格式为整数，请修正后重试！", rowIndex, columnName);
                        if (!string.IsNullOrEmpty(remark))
                        {
                            errMsg += "(注：" + remark + ")";
                        }
                        return false;
                    }
                }
            }
            //长度验证
            if (validLength)
            {
                int length = inputValue.Length;
                if (!isRequired)
                {
                    if (!string.IsNullOrEmpty(inputValue))
                    {
                        if (length > maxlength)
                        {
                            errMsg = String.Format("第{0}行{1}字符长度不可超过{2}个，请修正后重试！", rowIndex, columnName, maxlength);
                            return false;
                        }
                    }
                }
                else
                {
                    if (length > maxlength)
                    {
                        errMsg = String.Format("第{0}行{1}字符长度不可超过{2}个，请修正后重试！", rowIndex, columnName, maxlength);
                        return false;
                    }
                }
            }
            errMsg = "";
            return true;
        }
        /// <summary>
        /// 列名验证
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="dtColumns"></param>
        /// <param name="errMsg"></param>
        private bool ValidColumn(List<string> columns, List<string> dtColumns, out string errMsg)
        {
            var exceptList = columns.Except(dtColumns).ToList();
            if (exceptList.Count() > 0)
            {
                string notColumn = String.Join(',', exceptList.ToArray());
                errMsg = "导入的数据中，缺少" + notColumn + "数据列数据！";
                return false;
            }
            else
            {
                errMsg = "";
                return true;
            }
        }
        /// <summary>
        /// 列名验证
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="dt"></param>
        /// <param name="errMsg"></param>
        public bool ValidColumn(List<string> columns, DataTable dt, out string errMsg)
        {
            List<string> dtColumns = new List<string>();
            foreach (DataColumn column in dt.Columns)
            {
                dtColumns.Add(column.ColumnName);
            }
            var exceptList = columns.Except(dtColumns).ToList();
            if (exceptList.Count() > 0)
            {
                string notColumn = String.Join(',', exceptList.ToArray());
                errMsg = "导入的数据中，缺少" + notColumn + "数据列数据！";
                return false;
            }
            else
            {
                errMsg = "";
                return true;
            }
        }
        #endregion

        #region NPOIExcel导出基础设置
        /// <summary>
        /// 创建sheet
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheetName">sheet表名称</param>
        /// <returns></returns>
        public ISheet CreateSheet(HSSFWorkbook workBook, string sheetName = "")
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "导出数据";
            }
            ISheet sheet = workBook.CreateSheet(sheetName);
            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            return sheet;
        }
        /// <summary>
        /// 生成标题
        /// </summary>
        /// <param name="headers"></param>
        /// <param name="sheet"></param>
        /// <param name="headStyle"></param>
        public void SetHeaders(Dictionary<string, string> headers, ISheet sheet, ICellStyle headStyle)
        {
            string[] headersArrary = headers.Select(m => m.Value).ToArray();
            var rowIndex = 0;
            int columnIndex = 0;
            IRow row = sheet.CreateRow(rowIndex);
            foreach (string header in headersArrary)
            {
                ICell cell = row.CreateCell(columnIndex);
                cell.SetCellValue(header);
                cell.CellStyle = headStyle;
                columnIndex++;
            }
        }
        /// <summary>
        /// 默认列表头样式
        /// </summary>
        /// <param name="workBook"></param>
        /// <returns></returns>
        public ICellStyle CreateHeaderStyle(HSSFWorkbook workBook)
        {
            var headFont = workBook.CreateFont();
            headFont.IsBold = true;
            //标题列样式
            ICellStyle headStyle = workBook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.SetFont(headFont);
            return headStyle;
        }
        /// <summary>
        /// 默认单元格样式
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="isBold">文字是否加粗</param>
        /// <param name="isCenter">文字是否居中</param>
        /// <returns></returns>
        public ICellStyle CreateCellStyle(HSSFWorkbook workBook, bool isBold = false, bool isCenter = false)
        {
            var headFont = workBook.CreateFont();
            headFont.IsBold = isBold;
            //标题列样式
            ICellStyle cellStyle = workBook.CreateCellStyle();
            if (!isCenter)
            {
                cellStyle.Alignment = HorizontalAlignment.Left;
            }
            else
            {
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
            }
            //cellStyle.BorderBottom = BorderStyle.Thin;
            //cellStyle.BorderLeft = BorderStyle.Thin;
            //cellStyle.BorderRight = BorderStyle.Thin;
            //cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.SetFont(headFont);
            return cellStyle;
        }
        #endregion

        #region NPOI导出Excel
        /// <summary>
        /// 导出excel—单表sheet
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList">需要导出的数据源</param>
        /// <param name="columnTitle">标题名称</param>
        /// <param name="sheetName">sheet表名称</param>
        /// <returns></returns>
        public MemoryStream ExportExcel<T>(List<T> dataList, Dictionary<string, string> columnTitle, string sheetName = "")
        {
            PropertyInfo[] typePropertyInfo = typeof(T).GetProperties();//实体类信息
            string[] columnNames = columnTitle.Select(p => p.Key).ToArray();//字段名称
            //创建workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = CreateSheet(workbook, sheetName);
            ICellStyle headerStyle = CreateHeaderStyle(workbook);
            SetHeaders(columnTitle, sheet, headerStyle);
            //数据处理
            int rowIndex = 1;
            foreach (var dataItem in dataList)
            {
                IRow row = sheet.CreateRow(rowIndex);
                int columnIndex = 0;
                foreach (var columnName in columnNames)
                {
                    ICell rowCell = row.CreateCell(columnIndex);
                    //获取字段值
                    PropertyInfo propertyInfo = typePropertyInfo.FirstOrDefault(m => m.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
                    if (propertyInfo == null)
                    {
                        continue;
                    }
                    var cellValue = propertyInfo.GetValue(dataItem)?.ToString();
                    rowCell.SetCellValue(cellValue);
                    columnIndex++;
                }
                rowIndex++;
            }
            var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            return memoryStream;
        }
        /// <summary>
        /// sheet表数据写入—用于excel多sheet表创建
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="headerStyle">标题样式</param>
        /// <param name="dataList">需要导出的数据源</param>
        /// <param name="columnTitle">标题名称</param>
        public void WriteSheetData<T>(ISheet sheet, ICellStyle headerStyle, List<T> dataList, Dictionary<string, string> columnTitle)
        {
            PropertyInfo[] typePropertyInfo = typeof(T).GetProperties();//实体类信息
            string[] columnNames = columnTitle.Select(p => p.Key).ToArray();//字段名称
            SetHeaders(columnTitle, sheet, headerStyle);
            //数据处理
            int rowIndex = 1;
            foreach (var dataItem in dataList)
            {
                IRow row = sheet.CreateRow(rowIndex);
                int columnIndex = 0;
                foreach (var columnName in columnNames)
                {
                    ICell rowCell = row.CreateCell(columnIndex);
                    //获取字段值
                    PropertyInfo propertyInfo = typePropertyInfo.FirstOrDefault(m => m.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
                    if (propertyInfo == null)
                    {
                        continue;
                    }
                    var cellValue = propertyInfo.GetValue(dataItem)?.ToString();
                    rowCell.SetCellValue(cellValue);
                    columnIndex++;
                }
                rowIndex++;
            }
        }
        /// <summary>
        /// sheet表数据写入—用于excel多sheet表创建
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="dataList">需要导出的数据源</param>
        /// <param name="columnTitle">标题名称</param>
        ///  <param name="startRowIndex">数据开始行索引</param>
        public void WriteSheetData<T>(ISheet sheet, List<T> dataList, Dictionary<string, string> columnTitle, int startRowIndex)
        {
            PropertyInfo[] typePropertyInfo = typeof(T).GetProperties();//实体类信息
            string[] columnNames = columnTitle.Select(p => p.Key).ToArray();//字段名称
            //数据处理
            int rowIndex = startRowIndex;
            foreach (var dataItem in dataList)
            {
                IRow row = sheet.CreateRow(rowIndex);
                int columnIndex = 0;
                foreach (var columnName in columnNames)
                {
                    ICell rowCell = row.CreateCell(columnIndex);
                    //获取字段值
                    PropertyInfo propertyInfo = typePropertyInfo.FirstOrDefault(m => m.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
                    if (propertyInfo == null)
                    {
                        continue;
                    }
                    var cellValue = propertyInfo.GetValue(dataItem)?.ToString();
                    rowCell.SetCellValue(cellValue);
                    columnIndex++;
                }
                rowIndex++;
            }
        }
        /// <summary>
        /// sheet表数据写入—用于excel多sheet表创建
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="dataList">需要导出的数据源</param>
        /// <param name="columnTitle">标题名称</param>
        /// <param name="startRowIndex">数据开始行索引</param>
        /// <param name="colStyleIndex">数据某一列设置样式索引</param>
        /// <param name="colStyle">列单元格样式</param>
        public void WriteSheetData<T>(ISheet sheet, List<T> dataList, Dictionary<string, string> columnTitle, int startRowIndex, int colStyleIndex, ICellStyle colStyle)
        {
            PropertyInfo[] typePropertyInfo = typeof(T).GetProperties();//实体类信息
            string[] columnNames = columnTitle.Select(p => p.Key).ToArray();//字段名称
            //数据处理
            int rowIndex = startRowIndex;
            foreach (var dataItem in dataList)
            {
                IRow row = sheet.CreateRow(rowIndex);
                int columnIndex = 0;
                foreach (var columnName in columnNames)
                {
                    ICell rowCell = row.CreateCell(columnIndex);
                    //获取字段值
                    PropertyInfo propertyInfo = typePropertyInfo.FirstOrDefault(m => m.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
                    if (propertyInfo == null)
                    {
                        continue;
                    }
                    var cellValue = propertyInfo.GetValue(dataItem)?.ToString();
                    rowCell.SetCellValue(cellValue);
                    if (columnIndex == colStyleIndex)
                    {
                        rowCell.CellStyle = colStyle;
                    }
                    columnIndex++;
                }
                rowIndex++;
            }
        }
        #endregion
    }
}
